﻿Imports System.Text.RegularExpressions

Module Utils

    Public Function GenererClePrimaire(ByVal nomcle As String, ByVal nomtable As String) As Integer
        Dim cle As Integer
        MySQLConnect.MaRequete = "SELECT Max(" & nomcle & ") FROM " & nomtable
        MySQLConnect.MaCommandeSpecialRequete.CommandText = MySQLConnect.MaRequete
        MySQLConnect.MonRemplisseur.SelectCommand = MySQLConnect.MaCommandeSpecialRequete
        MySQLConnect.MonRemplisseur.Fill(MySQLConnect.MonJeuDeDonnees, "Max" & nomtable)
        If IsDBNull(MySQLConnect.MonJeuDeDonnees.Tables("Max" & nomtable).Rows(0).Item(0)) Then
            cle = 1
        Else
            cle = MySQLConnect.MonJeuDeDonnees.Tables("Max" & nomtable).Rows(0).Item(0) + 1
        End If
        MySQLConnect.MonJeuDeDonnees.Tables("Max" & nomtable).Clear()
        Return (cle)
    End Function

    Public Function RecupereridFournisseur(ByVal NomFournisseur As String) As Integer
        Dim idFournisseur As String
        MySQLConnect.MaRequete = "SELECT idFournisseur FROM fournisseur WHERE NomFournisseur = '" & NomFournisseur & "'"
        MySQLConnect.MaCommandeSpecialRequete.CommandText = MySQLConnect.MaRequete
        MySQLConnect.MonRemplisseur.SelectCommand = MySQLConnect.MaCommandeSpecialRequete
        MySQLConnect.MonRemplisseur.Fill(MySQLConnect.MonJeuDeDonnees, "idFournisseur")
        idFournisseur = MySQLConnect.MonJeuDeDonnees.Tables("idFournisseur").Rows(0).Item(0)
        MySQLConnect.MonJeuDeDonnees.Tables("idFournisseur").Clear()
        Return (idFournisseur)
    End Function

    Public Function RecupereridCategorie(ByVal NomCategorie As String) As Integer
        Dim idCategorie As Integer
        MySQLConnect.MaRequete = "SELECT idCategorie FROM categorie WHERE LibelleCategorie = '" & NomCategorie & "'"
        MySQLConnect.MaCommandeSpecialRequete.CommandText = MySQLConnect.MaRequete
        MySQLConnect.MonRemplisseur.SelectCommand = MySQLConnect.MaCommandeSpecialRequete
        MySQLConnect.MonRemplisseur.Fill(MySQLConnect.MonJeuDeDonnees, "idCategorie")
        idCategorie = MySQLConnect.MonJeuDeDonnees.Tables("idCategorie").Rows(0).Item(0)
        MySQLConnect.MonJeuDeDonnees.Tables("idCategorie").Clear()
        Return (idCategorie)
    End Function

    Public Function RecupereridClient(ByVal PrenomClient As String) As Integer
        Dim idClient As Integer
        MySQLConnect.MaRequete = "SELECT idClient FROM client WHERE PrenomClient = '" & PrenomClient & "'"
        MySQLConnect.MaCommandeSpecialRequete.CommandText = MySQLConnect.MaRequete
        MySQLConnect.MonRemplisseur.SelectCommand = MySQLConnect.MaCommandeSpecialRequete
        MySQLConnect.MonRemplisseur.Fill(MySQLConnect.MonJeuDeDonnees, "idClient")
        idClient = MySQLConnect.MonJeuDeDonnees.Tables("idClient").Rows(0).Item(0)
        MySQLConnect.MonJeuDeDonnees.Tables("idClient").Clear()
        Return (idClient)
    End Function

    Public Sub Rafraichir(ByVal NomTable As String)
        MySQLConnect.MonJeuDeDonnees.Tables(NomTable).Clear()
        FrmDemarrage.dgView.DataSource = Executer_Requete_Select("select * from " & NomTable, NomTable)
    End Sub

    Public Function RemplaceApostrophesSlash(ByRef unechaine As String) As String
        Dim ch As String
        ch = unechaine.Replace("'", "/")
        Return ch
    End Function

    Public Function RemplaceSlashApostrophes(ByRef unechaine As String) As String
        Dim ch As String
        ch = unechaine.Replace("/", "'")
        Return ch
    End Function

    Public Function RemplaceSlashApostrophesDataTable(ByRef lesdonnees As DataTable)
        Dim nblignes As Integer
        Dim nbcolonnes As Integer
        Dim test As String = ""

        nblignes = lesdonnees.Rows.Count
        nbcolonnes = lesdonnees.Columns.Count

        Dim i, j As Integer

        For i = 0 To nblignes - 1
            For j = 0 To nbcolonnes - 1
                'Attention le replace ne s'applique qu'aux type String : utilisation de  la classe de  base Object : 
                If Object.ReferenceEquals(lesdonnees.Rows(i).Item(j).GetType(), test.GetType()) Then
                    lesdonnees.Rows(i).Item(j) = RemplaceSlashApostrophes(lesdonnees.Rows(i).Item(j))
                End If
            Next
        Next
        Return lesdonnees
    End Function

    Public Function ValidCP(ByVal cp As String) As Boolean
        Dim myRegex As System.Text.RegularExpressions.Regex
        myRegex = New Regex("^([0-9]{5})$")
        Return myRegex.IsMatch(cp)
    End Function

    Public Function ValidChaine(ByVal text As String) As Boolean
        Dim myRegex As System.Text.RegularExpressions.Regex
        myRegex = New Regex("^[a-zA-Z]+$")
        Return myRegex.IsMatch(text)
    End Function
    ''' <summary>
    ''' Execution d'une requete d'insertion dans la BDD
    ''' </summary>
    ''' <param name="marequeteaction"></param>
    ''' <remarks></remarks>
    Public Sub Executer_Requete_Action(ByVal marequeteaction As String)
        Try
            MySQLConnect.MaCommandeSpecialRequete.CommandText = marequeteaction
            MySQLConnect.MaCommandeSpecialRequete.ExecuteNonQuery()
            MessageBox.Show("Requête Action correctement éxecutée...!", "Action", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Erreur dans la tentative d'insertion", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    ''' <summary>
    ''' Execution d'une requete scalaire ne renvoyant qu'un seul et unique résultat !
    ''' </summary>
    ''' <param name="marequetescalaire"></param>
    ''' <returns>Integer</returns>
    ''' <remarks></remarks>
    Public Function Executer_Requete_Scalaire(ByVal marequetescalaire As String) As Integer
        MySQLConnect.MaCommandeSpecialRequete.CommandText = marequetescalaire
        MySQLConnect.MonRemplisseur.SelectCommand = MySQLConnect.MaCommandeSpecialRequete
        Return CInt(MySQLConnect.MaCommandeSpecialRequete.ExecuteScalar)
    End Function
    ''' <summary>
    ''' Execute une requete SELECT
    ''' </summary>
    ''' <param name="marequete"></param>
    ''' <param name="nomvue"></param>
    ''' <returns>Data.DataTable</returns>
    ''' <remarks></remarks>
    Public Function Executer_Requete_Select(ByVal marequete As String, ByVal nomvue As String) As Data.DataTable
        Try
            MySQLConnect.MonJeuDeDonnees.Tables(nomvue).Clear()
        Catch ex As Exception

        End Try
        Dim result As New Data.DataTable
        MySQLConnect.MaCommandeSpecialRequete.CommandText = marequete
        MySQLConnect.MonRemplisseur.SelectCommand = MySQLConnect.MaCommandeSpecialRequete
        MySQLConnect.MaCommandeSpecialRequete.Connection = MySQLConnect.MaConnexion
        MySQLConnect.MonRemplisseur.Fill(MySQLConnect.MonJeuDeDonnees, nomvue)
        result = MySQLConnect.MonJeuDeDonnees.Tables(nomvue)
        Return result
    End Function
End Module
